#!/usr/bin/perl

use DBI;


$SRC_DB=$ENV{'SRC_DB'};
$TGT_DB=$ENV{'TGT_DB'};

#$SRC_DB='KEBCS_130';
#$TGT_DB='KEBCS_DHUB1';
sub s_META_LAYOUT_cnt
{
	my($fmt_name_) = @_;
	my $result = 100;

	$sql = "
SELECT 
COUNT(*)
FROM 	META_LAYOUT A
WHERE 
	A.LAYOUT_ID = '$fmt_name_'
";
  	$sth = $db->prepare($sql);

	$sth->execute();
  
	@data = $sth -> fetchrow_array;

	$sth->finish;
	$s_cnt = $data[0];
}

sub s_META_LAYOUT_FIELD_cnt
{
	my($fmt_name_, $field_id) = @_;
	my $result = 100;

	$sql = "
SELECT 
COUNT(*)
FROM 	META_LAYOUT_FIELD A
WHERE 
	A.LAYOUT_ID = '$fmt_name_'
AND	A.FIELD_ID = '$field_id'
";
  	$sth = $db->prepare($sql);

	$sth->execute();
  
	@data = $sth -> fetchrow_array;

#print "--------- s_META_LAYOUT_FIELD_cnt : $fmt_name_,$field_id,cnt:$data[0]\n";
	$sth->finish;
	$s_cnt = $data[0];
}

sub i_META_LAYOUT
{
	local(@i_para_) = @_;
print "--- i_META_LAYOUT ($i_para_[0])\n";
	$sql = "
INSERT INTO META_LAYOUT(
LAYOUT_ID,
LAYOUT_TYPE,
FIELD_DIVIDE,
RECORD_DIVIDE,
CHAR_FILLER,
NUMERIC_FILLER,
DESCRIPTION ,
AUTHOR_ID,
OPERATOR_ID
-- LAST_UPDATE_TIME
) VALUES (
?, ?, ?, ?, ?,
?, ?, ?, ?
)
";
  	$sth = $db->prepare($sql);

	$sth->execute(@i_para_);
  
	$sth->finish;
}

sub u_META_LAYOUT
{
print "--- u_META_LAYOUT ($i_para[0])\n";
	local(@i_para) = @_;

	$b_LAYOUT_ID		= $i_para[0];
	$b_LAYOUT_TYPE		= $i_para[1];
	$b_FIELD_DIVIDE		= $i_para[2];
	$b_RECORD_DIVIDE	= $i_para[3];
	$b_CHAR_FILLER		= $i_para[4];
	$b_NUMERIC_FILLER	= $i_para[5];
	$b_DESCRIPTION 		= $i_para[6];
	$b_AUTHOR_ID		= $i_para[7];
	$b_OPERATOR_ID		= $i_para[8];

	$sql = "
UPDATE META_LAYOUT
SET
	LAYOUT_TYPE	= '$b_LAYOUT_TYPE',
	FIELD_DIVIDE	= '$b_FIELD_DIVIDE',
	RECORD_DIVIDE	= '$b_RECORD_DIVIDE',
	CHAR_FILLER	= '$b_CHAR_FILLER',
	NUMERIC_FILLER	= '$b_NUMERIC_FILLER',
	DESCRIPTION	= '$b_DESCRIPTION',
	AUTHOR_ID	= '$b_AUTHOR_ID',
	OPERATOR_ID	= '$b_OPERATOR_ID'
WHERE
	LAYOUT_ID	= '$b_LAYOUT_ID'
";
  	$sth = $db->prepare($sql);

	$sth->execute();
  
	$sth->finish;
}

sub u_META_LAYOUT2
{
	local(@i_para_) = @_;
printf "--- update META_LAYOUT2\n";
	$sql = "
UPDATE META_LAYOUT
SET
	LAYOUT_TYPE	= ?,
	FIELD_DIVIDE	= ?
WHERE
	LAYOUT_ID	= ?
";
  	$sth = $db->prepare($sql);

	$sth->execute(@i_para_);
  
	$sth->finish;
}

sub u_META_LAYOUT_FIELD
{
	local(
$l_LAYOUT_ID,
$l_FIELD_ID,
$l_FIELD_SEQUENCE,
$l_FIELD_TYPE,
$l_FIELD_LENGTH,
$l_FIELD_PRECISION,
$l_FIELD_ARRAY_SIZE,
$l_FIELD_ATTRIBUTE,
$l_FIELD_ADDITIONAL_INFORMATION
) = @_;
#print "------------------------------- $l_FIELD_ID:$l_FIELD_LENGTH<\n";

	$sql = "
UPDATE META_LAYOUT_FIELD
SET
	FIELD_SEQUENCE		= '$l_FIELD_SEQUENCE',
	FIELD_TYPE		= '$l_FIELD_TYPE',
	FIELD_LENGTH		= '$l_FIELD_LENGTH',
	FIELD_PRECISION		= '$l_FIELD_PRECISION',
	FIELD_ARRAY_SIZE	= '$l_FIELD_ARRAY_SIZE',
	FIELD_ATTRIBUTE		= '$l_FIELD_ATTRIBUTE',
	FIELD_ADDITIONAL_INFORMATION = '$l_FIELD_ADDITIONAL_INFORMATION' 
WHERE
	LAYOUT_ID	= '$l_LAYOUT_ID'
AND	FIELD_ID	= '$l_FIELD_ID'
";
  	$sth = $db->prepare($sql);

	$sth->execute();
  
	$sth->finish;
}


sub i_META_LAYOUT_FIELD
{
	local(@i_para_) = @_;
print "--- i_META_LAYOUT_FIELD ($i_para_[0],$i_para_[1])\n";

	$sql = "
INSERT INTO META_LAYOUT_FIELD(
LAYOUT_ID,
FIELD_ID,
FIELD_SEQUENCE,
FIELD_TYPE,
FIELD_LENGTH,
FIELD_PRECISION,
FIELD_ARRAY_SIZE,
FIELD_ATTRIBUTE,
FIELD_ADDITIONAL_INFORMATION 
) VALUES (
?, ?, ?, ?, ?,
?, ?, ?, ?
)
";
  	$sth = $db->prepare($sql);

#$i_para_[2] = 1;
#$i_para_[4] = 1;
#$i_para_[5] = 1;
#$i_para_[6] = 1;

#printf "------LAYOUT_ID: $i_para_[0]<\n";
#printf "------FIELD_ID:$i_para_[1]<\n";
#printf "------FIELD_SEQUENCE:$i_para_[2]<\n";
#printf "------FIELD_TYPE:$i_para_[3]<\n";
#printf "------FIELD_LENGTH:$i_para_[4]<\n";
#printf "------FIELD_PRECISION:$i_para_[5]<\n";
#printf "------FIELD_ARRAY_SIZE:$i_para_[6]<\n";
#printf "------FIELD_ATTRIBUTE:$i_para_[7]<\n";
	$sth->execute(@i_para_);
  
	$sth->finish;
}

sub d_META_LAYOUT
{
	local($i_para_) = @_;

	$sql = "
DELETE META_LAYOUT
WHERE LAYOUT_ID = ?
";
  	$sth = $db->prepare($sql);

#print "sub d_META_LAYOUT() sql : $sql----$i_para_\n";
	$sth->execute($i_para_)
		or die "Couldn't execute statement: " . $sth->errstr;

#print "sub d_META_LAYOUT() executed..\n";
	$sth->finish;
}

sub d_META_LAYOUT_FIELD
{
	local($i_para_) = @_;

	$sql = "
DELETE META_LAYOUT_FIELD
WHERE LAYOUT_ID = ?
";
#print "sub d_META_LAYOUT_FIELD() sql : $sql----$i_para_\n";
  	$sth = $db->prepare($sql);

	$sth->execute($i_para_)
		or die "Couldn't execute statement: " . $sth->errstr;

#print "sub d_META_LAYOUT_FIELD() executed..\n";
	$sth->finish;
}

sub d_META_TRANSFORM_LAYOUT
{
	local($layout_id, $rule_id) = @_;

	$sql = "
DELETE META_TRANSFORM_LAYOUT
WHERE LAYOUT_ID = ?
";
  	$sth = $db->prepare($sql);

#print "sub d_META_TRANSFORM_LAYOUT() sql : $sql----$layout_id, $rule_id\n";
	$sth->execute($layout_id)
		or die "Couldn't execute statement: " . $sth->errstr;

#print "sub d_META_TRANSFORM_LAYOUT() executed..\n";
	$sth->finish;
}

#----------------------------------------------------------------
sub d_table
{
	my($table_name, $rule_name, $layout_id) = @_;

	if( ($table_name eq 'META_TRANSFORM_EXPRESSION') ||
		($table_name eq 'META_TRANSFORM_LAYOUT') ){
		$sub_sql01 = "
	TRANSFORM_RULE_ID 	= '$rule_name'
"
	} else {
		$sub_sql01 = "
	TRANSFORM_RULE_ID 	= '$rule_name'
AND	LAYOUT_ID 		= '$layout_id'
"
	}
	$sql = "
DELETE $table_name
WHERE 
$sub_sql01
";

#print "- d_table() : $table_name --$sql\n----\n";

  	$sth = $db->prepare($sql);

	$sth->execute()
		or die "Couldn't execute statement: " . $sth->errstr;

	$sth->finish;
}

#----------------------------------------------------------------

sub META_LAYOUT
{
	local($fmt_name, $rule_name) = @_;

#	$s_count = s_META_LAYOUT_cnt($fmt_name);
#print "- select count(*) META_LAYOUT : $s_count\n";
#	if( $s_count > 0 ) {
#		&d_table('META_TRANSFORM_LAYOUT', $rule_name, $fmt_name);
#		&d_table('META_TRANSFORM_EXPRESSION', $rule_name, $fmt_name);
#		&d_table('META_TRANSFORM_RULE', $rule_name, $fmt_name);
#
#		&d_META_LAYOUT_FIELD($fmt_name);
#		&d_META_TRANSFORM_LAYOUT($fmt_name, $rule_name);
#print "-META_LAYOUT()--------- $rule_name\n";
#		&d_META_LAYOUT($fmt_name);
#print "-META_LAYOUT()--------- $rule_name\n";
#	}

	$a_LAYOUT_ID		= $fmt_name;
	$a_LAYOUT_ID		=~ tr/-/_/;

	$a_LAYOUT_TYPE		= 'F';
	$a_FIELD_DIVIDE		= '2C';
	$a_RECORD_DIVIDE	= '00';
	$a_CHAR_FILLER		= ' ';
	$a_NUMERIC_FILLER	= ' ';
	$a_DESCRIPTION 		= 'Auto Migration';
	$a_AUTHOR_ID		= 'System';
	$a_OPERATOR_ID		= 'System';
	$a_LAST_UPDATE_TIME	= '';

	@i_para = (
	$a_LAYOUT_ID,
	$a_LAYOUT_TYPE,
	$a_FIELD_DIVIDE,
	$a_RECORD_DIVIDE,
	$a_CHAR_FILLER,
	$a_NUMERIC_FILLER,
	$a_DESCRIPTION,
	$a_AUTHOR_ID,
	$a_OPERATOR_ID
	#$l_LAST_UPDATE_TIME
	);

	$s_count = s_META_LAYOUT_cnt($a_LAYOUT_ID);
#print "- META_LAYOUT select count(*) META_LAYOUT($fmt_name) : $s_count\n";
	if( $s_count > 0 ) {
		&u_META_LAYOUT(@i_para);
	} else {
		&i_META_LAYOUT(@i_para);
	}
}

sub get_src_field_length
{
	local($src_fmt_name,$fld_name) = @_;

#	print " sub get_src_field_length ($src_fmt_name,$fld_name,$type_is_rev)\n";

	$result = 0;
	
	open(FILE_SRC, "<$TMP_DIR/$src_fmt_name.fmt");
	while( $line = <FILE_SRC> ) {
		chop $line;
		@val = split ',', $line;
		if ( $type_is_rev eq 'rev' ) {
			$tgt_fld = $val[4];
		} else {
			$tgt_fld = $val[2];
		}

		if( $tgt_fld eq $fld_name ) {
			$result = $val[6];
			last;
		}
	}
	close(FILE_SRC);
	$result = $result;
}
sub META_LAYOUT_FIELD
{
	local($fmt_name, $src_fmt_name) = @_;
#print "-----------------$fmt_name\n";
#print "                          $src_fmt_name\n";


	$l_field_sequence 	= 1;
	open(FILE_FMT, "<../data/$fmt_name.fmt");
	$i = 0;
	$m_size = 0;

	$repeat_ind = 0;
	$repeat_ind_old = 0;

	while( $line = <FILE_FMT> ) {
		chop $line;
		@val = split ',', $line;

#print "STEP loop\n";
		$l_LAYOUT_ID		= $val[0];
		$l_LAYOUT_ID		=~ tr/-/_/;

		$l_FIELD_ID 		= $val[2];
#		$l_FIELD_SEQUENCE 	= $val[1];
		$l_FIELD_SEQUENCE 	= $l_field_sequence;
		$l_field_sequence += 1;

		$l_FIELD_TYPE 		= 'x';
		$l_FIELD_LENGTH 	= $val[6];
		$l_FIELD_PRECISION 	= 0;
		$l_FIELD_ARRAY_SIZE 	= 0;
		$l_FIELD_ATTRIBUTE 	= ' ';
		$l_FIELD_ADDITIONAL_INFORMATION = '';

		printf "------ (:$l_LAYOUT_ID,:$l_FIELD_ID,$l_FIELD_SEQUENCE)\n";

		$repeat_ind = $val[8];
		$a_field_id = $val[7];
		printf "------ repeat_ind:$repeat_ind, a_field_id:$a_field_id\n";
		if( ($repeat_ind == 1) && ($old_repeat_ind == 0) ) {
#			print "THIS STEP\n";
# -----------------------------------------------------------------------
#printf "-1---------------- LAYOUT_ID	:$l_LAYOUT_ID\n";
			$a_field_type 		= 'L'; # Layout
			$a_field_array_size 	= $val[9];
			$a_field_length 	= 0;
			$a_repeat_term_id 	= $val[10];
			$a_field_additional_information	= $a_field_id;

#			printf "------ repeat_term_id:$a_repeat_term_id\n";
#printf "-2---------------- LAYOUT_ID	:$l_LAYOUT_ID\n";
			if( $a_repeat_term_id == 3 ) {
				$a_field_attribute = 'F';
			} else {
				# 1 : variable array
				$a_field_attribute = 'A';	

				$o_FIELD_ATTRIBUTE = 'I';
				$o_FIELD_ADDITIONAL_INFORMATION = $a_field_id;
#printf "-3---------------- LAYOUT_ID	:$l_LAYOUT_ID\n";

				@i_para = (
					$o_LAYOUT_ID		,
					$o_FIELD_ID 		,
					$o_FIELD_SEQUENCE 	,
					$o_FIELD_TYPE 		,
					$o_FIELD_LENGTH 	,
					$o_FIELD_PRECISION 	,
					$o_FIELD_ARRAY_SIZE 	,
					$o_FIELD_ATTRIBUTE 	,
					$o_FIELD_ADDITIONAL_INFORMATION 
				);
				&u_META_LAYOUT_FIELD(@i_para);
#printf "-5---------------- LAYOUT_ID	:$l_LAYOUT_ID\n";

			}

#printf "-6---------------- LAYOUT_ID	:$l_LAYOUT_ID\n";
			&META_LAYOUT($a_field_id);

#printf "-7---------------- LAYOUT_ID	:$l_LAYOUT_ID\n";
			@i_para = (
				$l_LAYOUT_ID		,
				$a_field_id 		,
				$l_FIELD_SEQUENCE 	,
				$a_field_type		,
				$a_field_length		,
				$l_FIELD_PRECISION 	,
				$a_field_array_size	,
				$a_field_attribute	,
				$a_field_additional_information
			);

		printf "-------------2-> ($l_LAYOUT_ID,$a_field_id,$l_FIELD_SEQUENCE)\n";

			$s_count = s_META_LAYOUT_FIELD_cnt($l_LAYOUT_ID, $a_field_id);
#printf "------ $l_LAYOUT_ID,$a_field_id,$l_FIELD_SEQUENCE,cnt:$s_count\n";
			if( $s_count > 0 ) {
				&u_META_LAYOUT_FIELD(@i_para);
			} else {
#printf "STEP THIS\n";
				&i_META_LAYOUT_FIELD(@i_para);
			}
			$l_FIELD_SEQUENCE = $l_field_sequence;
			$l_field_sequence += 1;
		}
# -----------------------------------------------------------------------
		$old_repeat_ind = $repeat_ind;


#C.FMT_NAME,
#B.REPEAT_IND,
#B.REPEAT_CNT,
#B.REPEAT_TERM_ID

$m_size += $l_FIELD_LENGTH;
#print "--$l_FIELD_LENGTH\n";

# Requirement 20091117, OC.SBST.0001.0004.DT, FF_APOB2C11-RDBS001_ON3,

		if( ($val[3] eq 'OC.SBST.0001.0004.DT') ||
			($val[3] eq 'OC.SBST.0005.0004.DT') ){
# Requirement.20091117
			$l_FIELD_LENGTH 	= 0;
		}

		if($val[3] eq 'OC.FLD.DT') {
# Requirement.20091120.except
			$l_FIELD_LENGTH 	= 0;
		}
#print "------>$val[3]<\n";

		if($val[3] eq 'OC.DFLT.DT') {
# Requirement.20091123
			$l_FIELD_LENGTH 	= 0;
		}
		if($val[3] eq 'OC.DFLT.F9') {
# Requirement.20091123
			$l_FIELD_LENGTH 	= 1;
		}
		if($val[3] eq 'OC.DFLT.H') {
# Requirement.20091123
			$l_FIELD_LENGTH 	= 1;
		}
		if( ($val[3] eq 'OC.DFLT.KCB') ||
			($val[3] eq 'OC.DFLT.KCB0') ||
			($val[3] eq 'OC.DFLT.SPACE.0015') ||
			($val[3] eq 'OC.DFLT.SPACE.0016') ||
			($val[3] eq 'OC.DFLT.SPACE.0007') ||
			($val[3] eq 'OC.DFLT.SPACE.0014') ||
			($val[3] eq 'OC.DFLT.SPACE.0008') ||
			($val[3] eq 'OC.DFLT.SPACE.0012') ||
			($val[3] eq 'OC.DFLT.SPACE.0003') ||
			($val[3] eq 'OC.DFLT.SPACE.0004') ||
			($val[3] eq 'OC.DFLT.ZERO.0003') 
		){
# Requirement.20091123
			$f_len = length( $l_FIELD_LENGTH ) / 2;
#print "############ length : $f_len\n";
			$l_FIELD_LENGTH 	= $f_len;

		}
# Requirement.20091215
#		printf "------ $l_FIELD_ID : $val[3] <- $val[4]\n";

		$strlen = length $val[3];
		$subs = substr $val[3], $strlen - 4, 4;
		if ( $subs eq '.END') {
#print " ----------- .END\n";
			$src_fld_name = $val[4];
			if ( $src_fld_name eq '' ) {
				$src_fld_name = $l_FIELD_ID;
			}

#print " ----------- $src_fmt_name, $src_fld_name<\n";
			$l_FIELD_LENGTH = get_src_field_length( $src_fmt_name, $src_fld_name);
#print " ----------- l_FIELD_LENGTH : $l_FIELD_LENGTH<\n";
		}

# Requirement.20091224
		if ( 	( 	($l_LAYOUT_ID eq 'FF_100000_IE1') && 
				($l_FIELD_ID eq 'STR0023') ) ||
			( 	($l_LAYOUT_ID eq 'FF_200000_IE1') && 
				($l_FIELD_ID eq 'STR0022') )
		) {
#print " ----------- # Requirement.20091224\n";
			$l_FIELD_TYPE 		= 'B';
		}

		if( $repeat_ind == 1 ) {
			$l_LAYOUT_ID		= $val[7];

				if( $type_is_rev eq 'not_rev' ) {
					$l_LAYOUT_ID		= $a_field_id;
				}
		}

		@i_para = (
			$l_LAYOUT_ID		,
			$l_FIELD_ID 		,
			$l_FIELD_SEQUENCE 	,
			$l_FIELD_TYPE 		,
			$l_FIELD_LENGTH 	,
			$l_FIELD_PRECISION 	,
			$l_FIELD_ARRAY_SIZE 	,
			$l_FIELD_ATTRIBUTE 	,
			$l_FIELD_ADDITIONAL_INFORMATION 
		);

		$o_LAYOUT_ID		= $l_LAYOUT_ID;
		$o_FIELD_ID 		= $l_FIELD_ID;
		$o_FIELD_SEQUENCE 	= $l_FIELD_SEQUENCE;
		$o_FIELD_TYPE 		= $l_FIELD_TYPE;
		$o_FIELD_LENGTH 	= $l_FIELD_LENGTH;
		$o_FIELD_PRECISION 	= $l_FIELD_PRECISION;
		$o_FIELD_ARRAY_SIZE 	= $l_FIELD_ARRAY_SIZE;
		$o_FIELD_ATTRIBUTE 	= $l_FIELD_ATTRIBUTE;
		$o_FIELD_ADDITIONAL_INFORMATION  = $l_FIELD_ADDITIONAL_INFORMATION;

		printf "-------META_LAYOUT_FIELD---> ($o_LAYOUT_ID,$o_FIELD_ID,$o_FIELD_SEQUENCE)\n";

		$s_count = s_META_LAYOUT_FIELD_cnt($l_LAYOUT_ID, $l_FIELD_ID);
		if( $s_count > 0 ) {
			&u_META_LAYOUT_FIELD(@i_para);
		} else {
#printf "STEP THAT\n";
			&i_META_LAYOUT_FIELD(@i_para);
		}

		$i += 1;
	}
	if( $val[3] eq 'OC.SBST.0001.DT' ) {
		@i_para01 = ( 
			'D',
			'1C',
			$l_LAYOUT_ID
		);

		&u_META_LAYOUT2(@i_para01);
	}
	close(FILE_FMT);
}

$TMP_DIR = '../data';
$fmt_name = @ARGV[0];
$src_fmt_name = @ARGV[1];
$type_is_rev = @ARGV[2];
#$rule_name = @ARGV[1];

print "2. migration format : $fmt_name\n";

$db = DBI->connect('DBI:ODBC:'.$TGT_DB, 'hubfmt', 'hubfmt');

&META_LAYOUT($fmt_name);
&META_LAYOUT_FIELD($fmt_name, $src_fmt_name);

$db->disconnect;

